/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package uit.tkorg.main;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import uit.tkorg.database.ConnectionManager;

/**
 *
 * @author tiendv
 * 
 * Chuan hoa title cho CSPubguru
 * 
 */
public class TitleStandardizedCSPubguru {
    
     public static void main(String[] args) throws SQLException { 
         String username="root";
         String pass="root";
         String port="3306";
         String database="pubguru";
         Connection connectionpool = ConnectionManager.getInstance(username, pass, port, database).getConnection();
         
         // String count row of paper:
         // "ALTER TABLE paper ADD titleStanded VARCHAR(1000);"

            for(int i=1; i< 3000000; i++) {
                String queryCountRowPaper = "SELECT * FROM Paper Where idPaper="+i;
                Statement stmt = connectionpool.createStatement();
                ResultSet resultSet = stmt.executeQuery(queryCountRowPaper);
                String title =null;
                while(resultSet.next())
                {
                    title = resultSet.getString("title");
                }
                resultSet.close();
                
                if(title!=null && title.length()<600)
                {
                    String titleStanded=title.replace(",", "").replace(".","").replace("/","").replace("?","").replace(":", "").replace("!","").replace("\\", "").replace("'", "").replace("-", "").replace(":", "").replace("\\", "").toLowerCase().replaceAll("\\s+", "");
                    String queryUpdate = "update paper set titleStanded =' "+ titleStanded +" ' where idPaper = "+ i;
                    stmt.execute(queryUpdate);
                }
                System.out.println(i);
            }
           connectionpool.close();
    }
}
